package com.b2c.repository.erp;

import com.b2c.entity.ErpGoodsStockInfoEntity;
import com.b2c.entity.erp.ErpGoodsSpecEntity;
import com.b2c.entity.erp.InvoiceEntity;
import com.b2c.entity.erp.InvoiceInfoEntity;
import com.b2c.entity.erp.enums.InvoiceBillStatusEnum;
import com.b2c.entity.result.EnumResultVo;
import com.b2c.entity.result.ResultVo;
import com.b2c.entity.ErpStockInCheckoutEntity;
import com.b2c.entity.ErpStockInCheckoutItemEntity;
import com.b2c.entity.ErpStockLocationEntity;
import com.b2c.repository.Tables;
import com.b2c.repository.utils.OrderNumberUtils;

import com.b2c.entity.enums.erp.EnumGoodsStockLogSourceType;
import com.b2c.entity.enums.erp.EnumGoodsStockLogType;
import com.b2c.entity.vo.ErpCheckoutStockInItemVo;
import com.b2c.entity.vo.ErpPurchaseStockInItemVo;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.transaction.interceptor.TransactionAspectSupport;
import org.springframework.util.StringUtils;

import java.sql.PreparedStatement;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.stream.LongStream;

/**
 * 描述：
 * 入库Repository
 *
 * @author qlp
 * @date 2019-09-20 09:45
 */
@Repository
public class StockInRepository {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Autowired
    DataSourceTransactionManager dataSourceTransactionManager;

    /**
     * 验货单入库数据校验
     *
     * @param checkoutId
     * @param items
     * @return
     */
    public ResultVo<Long> checkoutStockInDataChecked(Long checkoutId, List<ErpCheckoutStockInItemVo> items) {

        try {
            /***********1、查询验货单************/
            var form = jdbcTemplate.queryForObject("SELECT * FROM " + Tables.ErpStockInCheckout + " WHERE id=?", new BeanPropertyRowMapper<>(ErpStockInCheckoutEntity.class), checkoutId);

            /********2、判断传过来的items是否在验货表单中*********/
            var checkoutItems = jdbcTemplate.query("SELECT * FROM " + Tables.ErpStockInCheckoutItem + " WHERE checkoutId=?", new BeanPropertyRowMapper<>(ErpStockInCheckoutItemEntity.class), checkoutId);

            List<Long> itemIdList = new ArrayList<>();
            checkoutItems.forEach(n -> itemIdList.add(n.getId().longValue()));

            long[] itemIdArray = itemIdList.stream().mapToLong(t -> t.longValue()).toArray();

            for (var item : items) {
                boolean contains = LongStream.of(itemIdArray).anyMatch(x -> x == item.getCheckoutItemId());
                if (contains == false) {
//                    return -505;//参数错误，item不存在
                    return new ResultVo<>(EnumResultVo.ParamsError, "参数错误，itemId不在范围内[checkoutStockInDataChecked]");
                }
            }

        } catch (Exception e) {
            return new ResultVo<>(EnumResultVo.DataError, "验货表单不存在[checkoutStockInDataChecked]");
        }

        /*******仓位、商品校验*******/
        for (var item : items) {
            if (StringUtils.isEmpty(item.getLocationNumber()))
                return new ResultVo<>(EnumResultVo.DataError, item.getSpecNumber() + "请输入仓位信息[checkoutStockInDataChecked]");

            ErpStockLocationEntity stockLocation = null;
            try {
                /********* 查询仓库信息 ***********/
                stockLocation = jdbcTemplate.queryForObject("SELECT * FROM " + Tables.ErpStockLocation + " WHERE number=?", new BeanPropertyRowMapper<>(ErpStockLocationEntity.class), item.getLocationNumber());
            } catch (Exception e) {
                return new ResultVo<>(EnumResultVo.DataError, item.getLocationNumber() + "仓位不存在[checkoutStockInDataChecked]");
            }

            /**********        检查仓库仓位是否被占用         ***********/
            String sql2 = "SELECT * FROM " + Tables.ErpGoodsStockInfo + " WHERE locationId=? AND specId<>? AND isDelete=0";
            var s = jdbcTemplate.query(sql2, new BeanPropertyRowMapper<>(ErpGoodsStockInfoEntity.class), stockLocation.getId(), item.getSpecId());
            if (s != null && s.size() > 0) {
//                    return -2;//该仓位已经被占用了
                return new ResultVo<>(EnumResultVo.DataExist, item.getLocationNumber() + "仓位已经被占用[checkoutStockInDataChecked]");
            }

            ErpGoodsSpecEntity sku = new ErpGoodsSpecEntity();
            try {
                /**********        检查商品规格是否存在         ***********/
                sku = jdbcTemplate.queryForObject("SELECT * FROM " + Tables.ErpGoodsSpec + " WHERE id=?", new BeanPropertyRowMapper<>(ErpGoodsSpecEntity.class), item.getSpecId());
            } catch (Exception ex) {
                return new ResultVo<>(EnumResultVo.DataError, item.getSpecNumber() + "商品不存在[checkoutStockInDataChecked]");
            }

        }

        return new ResultVo<>(EnumResultVo.SUCCESS, "SUCCESS");
    }

    /**
     * 采购入库数据校验
     *
     * @param invoiceId
     * @param items
     * @return
     */
    public ResultVo<String> purchaseStockInDataChecked(Long invoiceId, List<ErpPurchaseStockInItemVo> items) {
        InvoiceEntity invoiceEntity = null;
        try {
            /***********1、查询采购单************/
            invoiceEntity = jdbcTemplate.queryForObject("SELECT * FROM " + Tables.ErpInvoice + " WHERE id=?", new BeanPropertyRowMapper<>(InvoiceEntity.class), invoiceId);
            if(invoiceEntity.getBillStatus().intValue()!= InvoiceBillStatusEnum.Audited.getIndex()){
                return new ResultVo<>(EnumResultVo.DataError, "该表单状态:"+InvoiceBillStatusEnum.getName(invoiceEntity.getBillStatus())+",不能操作入库");
            }
        } catch (Exception e) {
            return new ResultVo<>(EnumResultVo.DataError, "采购单不存在");
        }

        /********2、判断传过来的items是否在  采购单中*********/
        var checkoutItems = jdbcTemplate.query("SELECT * FROM " + Tables.ErpInvoiceInfo + " WHERE iid=?", new BeanPropertyRowMapper<>(InvoiceInfoEntity.class), invoiceId);

        List<Long> itemIdList = new ArrayList<>();
        checkoutItems.forEach(n -> itemIdList.add(n.getId().longValue()));

        long[] itemIdArray = itemIdList.stream().mapToLong(t -> t.longValue()).toArray();

        for (var item : items) {
            boolean contains = LongStream.of(itemIdArray).anyMatch(x -> x == item.getInvoiceInfoId());
            if (contains == false) {
//                    return -505;//参数错误，item不存在
                return new ResultVo<>(EnumResultVo.ParamsError, "参数错误，itemId不在范围内[purchaseStockInDataChecked]");
            }
        }


        /*******仓位、商品校验*******/
        for (var item : items) {
            ErpStockLocationEntity stockLocation = null;
            try {
                /********* 查询仓库信息 ***********/
                stockLocation = jdbcTemplate.queryForObject("SELECT * FROM " + Tables.ErpStockLocation + " WHERE id=?", new BeanPropertyRowMapper<>(ErpStockLocationEntity.class), item.getLocationId());

                item.setLocationNumber(stockLocation.getNumber());
                //setLocationId(stockLocation.getId());

            } catch (Exception e) {
                return new ResultVo<>(EnumResultVo.DataError, item.getLocationNumber() + "仓位不存在[purchaseStockInDataChecked]");
            }

            /**********        检查仓库仓位是否被占用         ***********/
            String sql2 = "SELECT * FROM " + Tables.ErpGoodsStockInfo + " WHERE locationId=? AND specId<>? AND isDelete=0";
            var s = jdbcTemplate.query(sql2, new BeanPropertyRowMapper<>(ErpGoodsStockInfoEntity.class), stockLocation.getId(), item.getSpecId());
            if (s != null && s.size() > 0) {
//                    return -2;//该仓位已经被占用了
                return new ResultVo<>(EnumResultVo.DataExist, item.getLocationNumber() + "仓位已经被占用[purchaseStockInDataChecked]");
            }

            ErpGoodsSpecEntity sku = new ErpGoodsSpecEntity();
            try {
                /**********        检查商品规格是否存在         ***********/
                sku = jdbcTemplate.queryForObject("SELECT * FROM " + Tables.ErpGoodsSpec + " WHERE id=? ", new BeanPropertyRowMapper<>(ErpGoodsSpecEntity.class), item.getSpecId());

                //重新赋值specNumber
                //item.setSpecNumber(sku.getSpecNumber());
                item.setSpecId(sku.getId());
                item.setGoodsId(sku.getGoodsId());

            } catch (Exception ex) {
                return new ResultVo<>(EnumResultVo.DataError, item.getSpecNumber() + "商品不存在[purchaseStockInDataChecked]");
            }

        }

        return new ResultVo<>(EnumResultVo.SUCCESS, "SUCCESS",invoiceEntity.getBillNo());
    }

    /**
     * 商品入库
     *
     * @param checkoutId      验货单id
     * @param items
     * @param stockInUserId   入库userid
     * @param stockInUserName 入库username
     * @return
     */
//    @Transactional
//    public ResultVo<Long> checkoutStockIn(Long checkoutId, List<ErpCheckoutStockInItemVo> items, Integer stockInUserId, String stockInUserName) {
//
//        String remark = "";//入库明细备注
//        Long invoiceId = 0l;//采购单号
//        try {
//            /***********1、查询验货单************/
//            var form = jdbcTemplate.queryForObject("SELECT * FROM " + Tables.ErpStockInCheckout + " WHERE id=?", new BeanPropertyRowMapper<>(ErpStockInCheckoutEntity.class), checkoutId);
//
//            invoiceId = form.getInvoiceId();
//
//            /********2、判断传过来的items是否在验货表单中*********/
//            var checkoutItems = jdbcTemplate.query("SELECT * FROM " + Tables.ErpStockInCheckoutItem + " WHERE checkoutId=?", new BeanPropertyRowMapper<>(ErpStockInCheckoutItemEntity.class), checkoutId);
//
//            List<Long> itemIdList = new ArrayList<>();
//            checkoutItems.forEach(n -> itemIdList.add(n.getId().longValue()));
//
//            long[] itemIdArray = itemIdList.stream().mapToLong(t -> t.longValue()).toArray();
//
//            for (var item : items) {
//                boolean contains = LongStream.of(itemIdArray).anyMatch(x -> x == item.getCheckoutItemId());
//                if (contains == false) {
////                    return -505;//参数错误，item不存在
//                    return new ResultVo<>(EnumResultVo.ParamsError, "参数错误，itemId不在范围内");
//                }
//            }
//
//            remark = "验货入库，验货单号：" + form.getNumber();
//
//
//        } catch (Exception e) {
////            return -404;
//            return new ResultVo<>(EnumResultVo.DataError, "验货表单不存在");
//        }
//
//        /*****************验证items 数据并重新赋值 字段****************/
//        for (var item : items) {
//            if (StringUtils.isEmpty(item.getLocationNumber())) return new ResultVo<>(EnumResultVo.DataError, "请输入仓位信息");
//            //仓位信息
//            try {
//                /********* 0000000001   查询仓库信息 ***********/
//                var stockLocation = jdbcTemplate.queryForObject("SELECT * FROM " + Tables.ErpStockLocation + " WHERE number=?", new BeanPropertyRowMapper<>(ErpStockLocationEntity.class), item.getLocationNumber());
//                //仓库id 赋值
//                item.setLocationId(stockLocation.getId());
//            } catch (Exception e) {
//                return new ResultVo<>(EnumResultVo.DataError, item.getLocationNumber() + "仓位不存在");
//            }
//
//            /**********       000000002  检查仓库仓位是否被占用         ***********/
//            String sql2 = "SELECT * FROM " + Tables.ErpGoodsStockInfo + " WHERE locationId=? AND specId<>?  AND isDelete=0";
//            var s = jdbcTemplate.query(sql2, new BeanPropertyRowMapper<>(ErpGoodsStockInfoEntity.class), item.getLocationId(), item.getSpecId());
//            if (s != null && s.size() > 0)
//                return new ResultVo<>(EnumResultVo.DataExist, item.getLocationNumber() + "仓位已经被占用");
//
//
//            /**********       000000003  检查商品规格是否存在         ***********/
//            try {
//                var sku = jdbcTemplate.queryForObject("SELECT * FROM " + Tables.ErpGoodsSpec + " WHERE id=?", new BeanPropertyRowMapper<>(ErpGoodsSpecEntity.class), item.getSpecId());
//                //重新赋值specNumber
//                item.setSpecNumber(sku.getSpecNumber());
//                item.setSpecId(sku.getId());
//                item.setGoodsId(sku.getGoodsId());
//
//            } catch (Exception ex) {
//                return new ResultVo<>(EnumResultVo.DataError, item.getSpecNumber() + "商品不存在");
//            }
//
//        }
//
//        try {
//
//
//            long total = 0;
//
//            /***************       更新核心表单信息     ***************/
//            for (var item : items) {
//
//                try {
//                    /*************    1、更新商品库存数据 erp_goods_stock_info    ***************/
//                    try {
//                        var goodsStock = jdbcTemplate.queryForObject("SELECT * FROM " + Tables.ErpGoodsStockInfo + " WHERE specId=? AND locationId=?  AND isDelete=0",
//                                new BeanPropertyRowMapper<>(ErpGoodsStockInfoEntity.class),
//                                item.getSpecId(),
//                                item.getLocationId());
//                        //存在，更新
//                        jdbcTemplate.update("UPDATE " + Tables.ErpGoodsStockInfo + " SET currentQty=currentQty+? WHERE id=?", item.getQuantity(), goodsStock.getId());
//
//                    } catch (Exception e) {
//                        //不存在，新建记录
//                        jdbcTemplate.update("INSERT INTO " + Tables.ErpGoodsStockInfo + " (goodsId,specId,specNumber,locationId,currentQty) VALUE (?,?,?,?,?)",
//                                item.getGoodsId(), item.getSpecId(), item.getSpecNumber(), item.getLocationId(), item.getQuantity());
//                    }
//
//
//                    /****************     2、更新商品库存erp_goods_spec       ****************/
//                    jdbcTemplate.update("UPDATE " + Tables.ErpGoodsSpec + " SET currentQty=currentQty+? WHERE id=? ", item.getQuantity(), item.getSpecId());
//
//
//                    /*************         3、加入库存日志erp_goods_stock_logs      *****************/
//                    remark += "  验货单明细ID：" + item.getCheckoutItemId();
//                    remark += "  SKU：" + item.getSpecNumber();
//
//                    String logsSQL = "INSERT INTO " + Tables.ErpGoodsStockLogs + " " +
//                            "(goodsId,specId,specNumber,locationId,quantity,createTime,type,sourceType,sourceId,remark,createUserId,createUserName,createOn) " +
//                            "VALUE (?,?,?,?,?,?,?,?,?,?,?,?,?)";
//
//                    jdbcTemplate.update(logsSQL,
//                            item.getGoodsId(), item.getSpecId(), item.getSpecNumber(), item.getLocationId(), item.getQuantity(),
//                            new Date(), EnumGoodsStockLogType.IN.getIndex(), EnumGoodsStockLogSourceType.CheckoutIn.getIndex(),
//                            checkoutId, remark, stockInUserId, stockInUserName, System.currentTimeMillis() / 1000);
//
//
//                    /******************  4、更新验货单明细信息 erp_stock_in_checkout_item   ******************/
//                    jdbcTemplate.update("UPDATE " + Tables.ErpStockInCheckoutItem + " SET inQuantity=inQuantity+? WHERE id=?", item.getQuantity(), item.getCheckoutItemId());
//
//                    total = total + item.getQuantity();
//                } catch (Exception e) {
//                    TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
//                    throw e;
//                }
//
//            }
//
//            /********************         5、 添加入库单信息       ***********************/
//
//            String sql1 = "INSERT INTO " + Tables.ErpStockInForm + " (number,checkoutId,isDelete,stockInUserId,stockInUserName,stockInTime,invoiceId,stockInTime1) VALUE (?,?,0,?,?,?,?,?)";
//            String formNum = "IN" + OrderNumberUtils.getOrderIdByTime();//入库单号
//            KeyHolder keyHolder = new GeneratedKeyHolder();
//
//            Long finalInvoiceId = invoiceId;
//
//            jdbcTemplate.update(connection -> {
//                PreparedStatement ps = connection.prepareStatement(sql1, Statement.RETURN_GENERATED_KEYS);
//                ps.setString(1, formNum);
//                ps.setLong(2, checkoutId);
//                ps.setInt(3, stockInUserId);
//                ps.setString(4, stockInUserName);
//                ps.setObject(5, new Date());
//                ps.setLong(6, finalInvoiceId);
//                ps.setLong(7, System.currentTimeMillis() / 1000);
//                return ps;
//            }, keyHolder);
//            Long formId = keyHolder.getKey().longValue();
//
//            for (var item : items) {
//                /***************   5.2 添加入库单明细数据 erp_stock_in_form_item***************/
//                jdbcTemplate.update("INSERT INTO " + Tables.ErpStockInFormItem + " (formId,goodsId,specId,quantity,locationId) VALUE (?,?,?,?,?)", formId, item.getGoodsId(), item.getSpecId(), item.getQuantity(), item.getLocationId());
//
//            }
//
//            /********** 6、更新验货单入库信息 erp_stock_in_checkout **********/
//            jdbcTemplate.update("UPDATE " + Tables.ErpStockInCheckout + " SET inQuantity=inQuantity+?,modifyOn=? WHERE id=?", total, checkoutId, System.currentTimeMillis() / 1000);
//
//            /*********** 7、如果入库数量满了则更新状态 ***********/
//            jdbcTemplate.update("UPDATE " + Tables.ErpStockInCheckout + " SET stockInStatus=1,modifyOn=? WHERE id=? AND quantity=inQuantity", checkoutId, System.currentTimeMillis() / 1000);
//
//
//            return new ResultVo<>(EnumResultVo.SUCCESS, "SUCCESS", total);
//        } catch (Exception e) {
//            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
//            return new ResultVo<>(EnumResultVo.SystemException, e.getMessage());
//        }
//
////        /*********查询采购单是否存在*********/
////        var invoice = jdbcTemplate.query("SELECT * FROM " + Tables.ErpInvoice + " WHERE id=?", new BeanPropertyRowMapper<>(InvoiceEntity.class), vo.getInvoiceId());
////        if (invoice == null || invoice.size() == 0) return 0;//表单不存在
////
////        var invoiceInfo = jdbcTemplate.query("SELECT * FROM " + Tables.ErpInvoiceInfo + " WHERE id=? ", new BeanPropertyRowMapper<>(InvoiceInfoEntity.class), vo.getInvoiceInfoId());
////        if (invoiceInfo == null || invoiceInfo.size() == 0) return 0;//表单明细不存在
////
////        if (invoiceInfo.get(0).getQualifiedQuantity() <= invoiceInfo.get(0).getInQuantity()) {
////            //已入库完，不能再入库了
////            return -1;//表单明细不存在
////        }
////
////        /**********检查仓库仓位是否被占用***********/
////        String sql2 = "SELECT * FROM " + Tables.ErpGoodsStockInfo + " WHERE locationId=? AND specId<>?";
////        var s = jdbcTemplate.query(sql2, new BeanPropertyRowMapper<>(ErpGoodsStockInfoEntity.class), vo.getShelfId(), vo.getSpecId());
////        if (s != null && s.size() > 0) return -2;//该仓位已经被占用了
////
////        /*************1、更新商品库存数据 erp_goods_stock_info ***************/
////        try {
////            var goodsStock = jdbcTemplate.queryForObject("SELECT * FROM " + Tables.ErpGoodsStockInfo + " WHERE goodsId=? AND specId=? AND locationId=? ", new BeanPropertyRowMapper<>(ErpGoodsStockInfoEntity.class), vo.getGoodsId(), vo.getSpecId(), vo.getShelfId());
////            //存在，更新
////            jdbcTemplate.update("UPDATE " + Tables.ErpGoodsStockInfo + " SET currentQty=currentQty+? WHERE id=?", vo.getQuantity(), goodsStock.getId());
////
////        } catch (Exception e) {
////            //不存在，新建记录
////            jdbcTemplate.update("INSERT INTO " + Tables.ErpGoodsStockInfo + " (goodsId,goodsNumber,specId,specNumber,locationId,currentQty) VALUE (?,?,?,?,?,?)",
////                    vo.getGoodsId(), vo.getGoodsNumber(), vo.getSpecId(), vo.getSpecNumber(), vo.getShelfId(), vo.getQuantity());
////        }
////
////
////        /***2、更新商品库存erp_goods_spec***/
////        jdbcTemplate.update("UPDATE " + Tables.ErpGoodsSpec + " SET currentQty=currentQty+? WHERE id=? ", vo.getQuantity(), vo.getSpecId());
////
////
////        /*************加入库存日志erp_goods_stock_logs*****************/
////        String remark = "采购入库，单号：" + invoice.get(0).getBillNo();
////        remark += "  采购单明细ID：" + vo.getInvoiceInfoId();
////        remark += "  SKU：" + vo.getSpecNumber();
////
////        String logsSQL = "INSERT INTO " + Tables.ErpGoodsStockLogs + " (goodsId,goodsNumber,specId,specNumber,locationId,quantity,createTime,type,source,remark) VALUE (?,?,?,?,?,?,?,?,?,?)";
////        jdbcTemplate.update(logsSQL,
////                vo.getGoodsId(), vo.getGoodsNumber(), vo.getSpecId(), vo.getSpecNumber(), vo.getShelfId(), vo.getQuantity(),
////                new Date(), EnumGoodsStockLogType.IN.getIndex(), "PurchaseIn", remark);
////
////
////        /*****3、更新采购单明细信息*****/
////        int r3 = jdbcTemplate.update("UPDATE " + Tables.ErpInvoiceInfo + " SET inQuantity=inQuantity+?,locationId=? WHERE id=?", vo.getQuantity(), vo.getShelfId(), vo.getInvoiceInfoId());
////
////        /****4、更新invoice表单已入库数量*****/
////        int r1 = jdbcTemplate.update("UPDATE " + Tables.ErpInvoice + " SET inQuantity=inQuantity+? WHERE id=?", vo.getQuantity(), vo.getInvoiceId());
////
////        /****5、如果数量已用完，更新表单为已入库*****/
////        int r2 = jdbcTemplate.update("UPDATE " + Tables.ErpInvoice + " SET billStatus=?,stockInName=?,stockInTime=? WHERE id=? AND qualifiedQuantity=inQuantity", InvoiceBillStatusEnum.StockIn.getIndex(), name, new Date().getTime(), vo.getInvoiceId());
////
////        return total;
//    }

    /**
     * 采购入库
     *
     * @param invoiceId
     * @param qcReport
     * @param qcInspector
     * @param items
     * @param stockInUserId
     * @param stockInUserName
     * @return
     */
    @Transactional
    public ResultVo<Long> purchaseStockIn(Long invoiceId, Integer qcReport, String qcInspector, List<ErpPurchaseStockInItemVo> items, Integer stockInUserId, String stockInUserName) {


        var checkedResult = purchaseStockInDataChecked(invoiceId, items);

        if (checkedResult.getCode() != EnumResultVo.SUCCESS.getIndex()) {
            return new ResultVo<>(EnumResultVo.DataError, checkedResult.getMsg());
        }
        String remark = "采购入库，采购id" + invoiceId;//入库明细备注


        try {
            /********************         5、 添加入库单信息       ***********************/

            String sql1 = "INSERT INTO " + Tables.ErpStockInForm + " (number,sourceNo,isDelete,stockInUserId,stockInUserName,invoiceId,stockInTime1,qcInspector,hasQcReport,inType) VALUE (?,?,0,?,?,?,?,?,?,?)";
            String formNum = "IN" + OrderNumberUtils.getOrderIdByTime();//入库单号

            KeyHolder keyHolderStocInForm = new GeneratedKeyHolder();


            jdbcTemplate.update(connection -> {
                PreparedStatement ps = connection.prepareStatement(sql1, Statement.RETURN_GENERATED_KEYS);
                ps.setString(1, formNum);
                ps.setString(2, checkedResult.getData());
                ps.setInt(3, stockInUserId);
                ps.setString(4, stockInUserName);
                ps.setLong(5, invoiceId);
                ps.setLong(6, System.currentTimeMillis() / 1000);
                ps.setString(7, qcInspector);
                ps.setInt(8, qcReport);
                ps.setInt(9, 1);//入库类型1采购入库2退货入库3盘点入库
                return ps;
            }, keyHolderStocInForm);
            //入库单id
            Long stockInFormId = keyHolderStocInForm.getKey().longValue();


            long totalInQuantity = 0;//总入库数量
            long currQty = 0;//当前库存数量

            /***************       更新核心表单信息     ***************/
            for (var item : items) {
                if(item.getQuantity().longValue() > 0) {
                    try {
                        /*************    1、更新商品库存数据 erp_goods_stock_info    ***************/
                        Long erpGoodsStockInfoId = 0L;
                        try {
                            var goodsStock = jdbcTemplate.queryForObject("SELECT * FROM " + Tables.ErpGoodsStockInfo + " WHERE specId=? AND locationId=? AND isDelete=0",
                                    new BeanPropertyRowMapper<>(ErpGoodsStockInfoEntity.class),
                                    item.getSpecId(),
                                    item.getLocationId());
                            //存在，更新
                            currQty = goodsStock.getCurrentQty();

                            jdbcTemplate.update("UPDATE " + Tables.ErpGoodsStockInfo + " SET currentQty=currentQty+? WHERE id=?", item.getQuantity(), goodsStock.getId());
                            erpGoodsStockInfoId = goodsStock.getId();
                        } catch (Exception e) {
                            //不存在，插入新记录
                            currQty = 0;
                            KeyHolder keyHolder = new GeneratedKeyHolder();
                            String addGoodsStock = "INSERT INTO " + Tables.ErpGoodsStockInfo + " (goodsId,specId,specNumber,locationId,currentQty) VALUE (?,?,?,?,?)";
                            jdbcTemplate.update(connection -> {
                                PreparedStatement ps = connection.prepareStatement(addGoodsStock, Statement.RETURN_GENERATED_KEYS);
                                ps.setInt(1, item.getGoodsId());
                                ps.setInt(2, item.getSpecId());
                                ps.setString(3, item.getSpecNumber());
                                ps.setInt(4, item.getLocationId());
                                ps.setLong(5, item.getQuantity());
                                return ps;
                            }, keyHolder);
                            //入库单id
                            erpGoodsStockInfoId = keyHolder.getKey().longValue();
                        }

                        jdbcTemplate.update("INSERT INTO " + Tables.ErpGoodsStockInfoItem + " (stockInfoId,currentQty,purPrice,invoiceId,invoiceInfoId,stockInFormId,specId,inQty) VALUE (?,?,?,?,?,?,?,?)"
                                , erpGoodsStockInfoId, item.getQuantity(), item.getPrice(), invoiceId, item.getInvoiceInfoId(),stockInFormId,item.getSpecId(),item.getQuantity());

                        /****************     2、更新商品库存erp_goods_spec       ****************/
                        jdbcTemplate.update("UPDATE " + Tables.ErpGoodsSpec + " SET currentQty=currentQty+? WHERE id=? ", item.getQuantity(), item.getSpecId());


                        /*************         3、加入库存日志erp_goods_stock_logs      *****************/
                        remark += "  采购单明细ID：" + item.getInvoiceInfoId();
                        remark += "  SKU：" + item.getSpecNumber();


                        String logsSQL = "INSERT INTO " + Tables.ErpGoodsStockLogs + " " +
                                "(goodsId,specId,specNumber,locationId,quantity,createTime,type,sourceType,sourceId,remark,createUserId,createUserName,createOn,currQty,goodsNumber) " +
                                "VALUE (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

                        jdbcTemplate.update(logsSQL,
                                item.getGoodsId(), item.getSpecId(), item.getSpecNumber(), item.getLocationId(), item.getQuantity(),
                                new Date(), EnumGoodsStockLogType.IN.getIndex(), EnumGoodsStockLogSourceType.PurchaseStockIn.getIndex(),
                                invoiceId, remark, stockInUserId, stockInUserName, System.currentTimeMillis() / 1000, (currQty + item.getQuantity()),item.getGoodsNumber());


                        /******************  4、更新采购单明细信息 erp_invoice_info   ******************/
                        jdbcTemplate.update("UPDATE " + Tables.ErpInvoiceInfo + " SET inQuantity=inQuantity+? WHERE id=?", item.getQuantity(), item.getInvoiceInfoId());
                        jdbcTemplate.update("UPDATE " + Tables.ErpInvoiceInfo + " SET status=3 WHERE id=? AND status=1 AND inQuantity=quantity", item.getInvoiceInfoId() );

                        totalInQuantity += item.getQuantity();


                    } catch (Exception e) {
                        TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                        throw e;
                    }


                    /***************   5.2 添加入库单明细数据 erp_stock_in_form_item***************/
                    jdbcTemplate.update("INSERT INTO " + Tables.ErpStockInFormItem + " (formId,goodsId,specId,quantity,locationId,itemId) VALUE (?,?,?,?,?,?)", stockInFormId, item.getGoodsId(), item.getSpecId(), item.getQuantity(), item.getLocationId(), item.getInvoiceInfoId());
                
                }
            }

            

            // for (var item : items) {
            //     if(item.getQuantity().longValue() > 0) {
            //         /***************   5.2 添加入库单明细数据 erp_stock_in_form_item***************/
            //         jdbcTemplate.update("INSERT INTO " + Tables.ErpStockInFormItem + " (formId,goodsId,specId,quantity,locationId,itemId) VALUE (?,?,?,?,?,?)", formId, item.getGoodsId(), item.getSpecId(), item.getQuantity(), item.getLocationId(), item.getInvoiceInfoId());
            //     }
            // }

            /********** 6、更新采购单入库信息 erp_invoice **********/
            jdbcTemplate.update("UPDATE " + Tables.ErpInvoice + " SET inQuantity=inQuantity+? WHERE id=?", totalInQuantity, invoiceId);
            int c = jdbcTemplate.queryForObject("SELECT COUNT(0) FROM "+Tables.ErpInvoiceInfo+" WHERE iid=? AND status<>3",Integer.class,invoiceId);
            if(c==0){
                //全部入库了，更新状态
                jdbcTemplate.update("UPDATE " + Tables.ErpInvoice + " SET billStatus=3 WHERE id=?", invoiceId);
            }

//            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            return new ResultVo<>(EnumResultVo.SUCCESS, "SUCCESS", totalInQuantity);
        } catch (Exception e) {
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            return new ResultVo<>(EnumResultVo.SystemException, e.getMessage());
        }


    }
}
